package com.dsl.dslsuzhou.engine;

import com.dsl.dslsuzhou.model.entity.dsl.*;
import org.springframework.util.CollectionUtils;
import org.springframework.util.StringUtils;

import java.time.LocalDateTime;
import java.time.format.DateTimeFormatter;
import java.util.ArrayList;
import java.util.List;
import java.util.regex.Pattern;

/**
 * DSL SQL构建器
 */
public class DslSqlBuilder {
    
    private static final DateTimeFormatter DATETIME_FORMATTER = DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss");
    private static final Pattern SAFE_EXPR_PATTERN = Pattern.compile("^[a-zA-Z0-9_.,+\\-*/()\\s]+$");
    
    /**
     * 构建查询SQL
     */
    public static BuiltSql buildQuery(ChartDsl dsl) {
        validateDsl(dsl);

        // 检查是否需要对比分析
        boolean needCompare = dsl.getCompare() != null &&
                             (Boolean.TRUE.equals(dsl.getCompare().getYoy()) ||
                              Boolean.TRUE.equals(dsl.getCompare().getMom()));

        // 检查是否需要占比分析
        boolean needProportion = dsl.getCompare() != null &&
                                Boolean.TRUE.equals(dsl.getCompare().getProportion());

        if (needProportion) {
            return buildProportionQuery(dsl);
        } else if (needCompare) {
            return buildCompareQuery(dsl);
        } else {
            return buildNormalQuery(dsl);
        }
    }

    /**
     * 构建普通查询SQL
     */
    private static BuiltSql buildNormalQuery(ChartDsl dsl) {
        Dataset dataset = dsl.getDataset();
        TimeRange timeRange = TimeRangeCalc.calculate(dsl.getTime().getWindow());
        Granularity granularity = Granularity.fromCode(dsl.getGranularity());

        List<Object> parameters = new ArrayList<>();
        StringBuilder sql = new StringBuilder();

        // 是否需要时间轴补齐
        boolean needTimeFill = dsl.getFill() != null &&
                              Boolean.TRUE.equals(dsl.getFill().getCompleteTimeAxis());

        if (needTimeFill) {
            // 生成时间序列CTE
            sql.append(FillUtil.generateTimeSeriesCte(timeRange, granularity));
            sql.append(",\n");
            // 主查询CTE
            sql.append("data_query AS (\n");
        } else {
            // 没有时间轴补齐时，需要添加WITH关键字
            sql.append("WITH data_query AS (\n");
        }
        sql.append("  SELECT \n");
        sql.append("    ").append(granularity.getBucketExpr(dataset.getTimeField())).append(" AS bucket");
        
        // 添加维度字段
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                String selectExpr = getSelectExpression(dim);
                if (StringUtils.hasText(selectExpr)) {
                    sql.append(",\n    ").append(selectExpr).append(" AS ").append(dim.getName());
                }
            }
        }
        
        // 添加指标字段
        if (!CollectionUtils.isEmpty(dsl.getMetrics())) {
            for (MetricSpec metric : dsl.getMetrics()) {
                sql.append(",\n    ");
                String expr = StringUtils.hasText(metric.getExpr()) ? metric.getExpr() : dataset.getValueField();
                validateExpression(expr);
                sql.append(metric.getAgg()).append("(").append(expr).append(") AS ").append(metric.getName());
            }
        } else {
            // 默认指标
            sql.append(",\n    SUM(").append(dataset.getValueField()).append(") AS value");
        }
        
        sql.append("\n  FROM ").append(dataset.getTable()).append(" f");

        // 检查是否需要JOIN维度表
        boolean needFloorJoin = needsFloorJoin(dsl);
        boolean needBuildingJoin = needsBuildingJoin(dsl);
        boolean needDeviceJoin = needsDeviceJoin(dsl);

        if (needDeviceJoin) {
            sql.append("\n  LEFT JOIN dim_device dd ON f.device_id = dd.device_id");
        }
        if (needFloorJoin) {
            sql.append("\n  LEFT JOIN dim_floor df ON f.floor_id = df.floor_id");
        }
        if (needBuildingJoin) {
            sql.append("\n  LEFT JOIN dim_building db ON df.building_id = db.building_id");
        }

        // 添加自定义JOIN子句
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                if (StringUtils.hasText(dim.getJoin()) && StringUtils.hasText(dim.getOn())) {
                    sql.append("\n  LEFT JOIN ").append(dim.getJoin()).append(" ON ").append(dim.getOn());
                }
            }
        }
        sql.append("\n  WHERE f.").append(dataset.getTimeField()).append(" >= ?");
        sql.append("\n    AND f.").append(dataset.getTimeField()).append(" < ?");
        
        parameters.add(timeRange.getStart().format(DATETIME_FORMATTER));
        parameters.add(timeRange.getEnd().format(DATETIME_FORMATTER));
        
        // 添加过滤条件
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                sql.append("\n    AND ");
                buildFilterCondition(sql, filter, parameters);
            }
        }
        
        sql.append("\n  GROUP BY bucket");
        
        // 添加维度分组
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                String selectExpr = getSelectExpression(dim);
                if (StringUtils.hasText(selectExpr)) {
                    sql.append(", ").append(dim.getName());
                }
            }
        }
        
        sql.append("\n)");
        
        // 最终查询
        if (needTimeFill) {
            sql.append("\nSELECT \n");
            sql.append("  ts.bucket");
            
            if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
                for (DimensionSpec dim : dsl.getDimensions()) {
                    if (StringUtils.hasText(dim.getSelect())) {
                        sql.append(",\n  COALESCE(dq.").append(dim.getName()).append(", '') AS ").append(dim.getName());
                    }
                }
            }
            
            if (!CollectionUtils.isEmpty(dsl.getMetrics())) {
                for (MetricSpec metric : dsl.getMetrics()) {
                    boolean fillZero = dsl.getFill() != null && Boolean.TRUE.equals(dsl.getFill().getFillZero());
                    String defaultValue = fillZero ? "0" : "NULL";
                    sql.append(",\n  COALESCE(dq.").append(metric.getName()).append(", ").append(defaultValue).append(") AS ").append(metric.getName());
                }
            } else {
                boolean fillZero = dsl.getFill() != null && Boolean.TRUE.equals(dsl.getFill().getFillZero());
                String defaultValue = fillZero ? "0" : "NULL";
                sql.append(",\n  COALESCE(dq.value, ").append(defaultValue).append(") AS value");
            }
            
            sql.append("\nFROM time_series ts");
            sql.append("\nLEFT JOIN data_query dq ON ts.bucket = dq.bucket");
        } else {
            sql.append("\nSELECT * FROM data_query");
        }
        
        // 添加排序
        sql.append("\nORDER BY ");
        if (!CollectionUtils.isEmpty(dsl.getOrderBy())) {
            for (int i = 0; i < dsl.getOrderBy().size(); i++) {
                if (i > 0) sql.append(", ");
                OrderSpec order = dsl.getOrderBy().get(i);
                sql.append(order.getField()).append(" ").append(order.getDir());
            }
        } else {
            sql.append("bucket ASC");
        }
        
        return new BuiltSql(sql.toString(), parameters);
    }
    
    /**
     * 构建过滤条件
     */
    private static void buildFilterCondition(StringBuilder sql, FilterSpec filter, List<Object> parameters) {
        String field = getFilterFieldExpression(filter.getField());
        String op = filter.getOp();
        
        switch (op) {
            case "eq":
                sql.append(field).append(" = ?");
                parameters.add(filter.getValue());
                break;
            case "ne":
                sql.append(field).append(" != ?");
                parameters.add(filter.getValue());
                break;
            case "gt":
                sql.append(field).append(" > ?");
                parameters.add(filter.getValue());
                break;
            case "lt":
                sql.append(field).append(" < ?");
                parameters.add(filter.getValue());
                break;
            case "gte":
                sql.append(field).append(" >= ?");
                parameters.add(filter.getValue());
                break;
            case "lte":
                sql.append(field).append(" <= ?");
                parameters.add(filter.getValue());
                break;
            case "like":
                sql.append(field).append(" LIKE ?");
                parameters.add("%" + filter.getValue() + "%");
                break;
            case "in":
                if (CollectionUtils.isEmpty(filter.getValues())) {
                    throw new IllegalArgumentException("Values cannot be empty for 'in' operation");
                }
                sql.append(field).append(" IN (");
                for (int i = 0; i < filter.getValues().size(); i++) {
                    if (i > 0) sql.append(", ");
                    sql.append("?");
                    parameters.add(filter.getValues().get(i));
                }
                sql.append(")");
                break;
            default:
                throw new IllegalArgumentException("Unsupported filter operation: " + op);
        }
    }
    
    /**
     * 验证DSL配置
     */
    private static void validateDsl(ChartDsl dsl) {
        if (dsl == null) {
            throw new IllegalArgumentException("ChartDsl cannot be null");
        }
        if (dsl.getDataset() == null) {
            throw new IllegalArgumentException("Dataset cannot be null");
        }
        if (dsl.getTime() == null || dsl.getTime().getWindow() == null) {
            throw new IllegalArgumentException("Time window cannot be null");
        }
        if (!StringUtils.hasText(dsl.getGranularity())) {
            throw new IllegalArgumentException("Granularity cannot be empty");
        }
    }
    
    /**
     * 验证表达式安全性
     */
    private static void validateExpression(String expr) {
        if (!StringUtils.hasText(expr)) {
            return;
        }
        if (!SAFE_EXPR_PATTERN.matcher(expr).matches()) {
            throw new IllegalArgumentException("Invalid expression: " + expr);
        }
    }

    /**
     * 检查是否需要JOIN dim_floor表
     */
    private static boolean needsFloorJoin(ChartDsl dsl) {
        // 检查dimensions中是否有building_id或area_id
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                if ("building_id".equals(dim.getName()) || "area_id".equals(dim.getName())) {
                    return true;
                }
            }
        }

        // 检查filters中是否有building_id或area_id
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                String field = filter.getField();
                if (field.contains("building_id") || field.contains("area_id")) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 检查是否需要JOIN dim_building表
     */
    private static boolean needsBuildingJoin(ChartDsl dsl) {
        // 检查dimensions中是否有area_id
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                if ("area_id".equals(dim.getName())) {
                    return true;
                }
            }
        }

        // 检查filters中是否有area_id
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                String field = filter.getField();
                if (field.contains("area_id")) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 检查是否需要JOIN dim_device表
     */
    private static boolean needsDeviceJoin(ChartDsl dsl) {
        // 检查dimensions中是否有device_type或device_category
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                if ("device_type".equals(dim.getName()) || "device_category".equals(dim.getName()) ||
                    "device_name".equals(dim.getName()) || "device_model".equals(dim.getName())) {
                    return true;
                }
            }
        }

        // 检查filters中是否有device_type或device_category
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                if ("device_type".equals(filter.getField()) || "device_category".equals(filter.getField()) ||
                    "device_name".equals(filter.getField()) || "device_model".equals(filter.getField())) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 获取维度字段的SELECT表达式
     */
    private static String getSelectExpression(DimensionSpec dim) {
        // 如果已经配置了select表达式，直接使用
        if (StringUtils.hasText(dim.getSelect())) {
            return dim.getSelect();
        }

        // 根据维度名称自动映射
        String dimName = dim.getName();
        if ("area_id".equals(dimName)) {
            return "db.area_id";
        } else if ("building_id".equals(dimName)) {
            return "df.building_id";
        } else if ("floor_id".equals(dimName)) {
            return "f.floor_id";
        } else if ("device_type".equals(dimName)) {
            return "dd.device_type";
        } else if ("device_category".equals(dimName)) {
            return "dd.device_category";
        } else if ("device_name".equals(dimName)) {
            return "dd.device_name";
        } else if ("device_model".equals(dimName)) {
            return "dd.device_model";
        }

        return null;
    }

    /**
     * 获取过滤字段的表达式
     */
    private static String getFilterFieldExpression(String fieldName) {
        // 如果字段名已经包含表别名，直接使用
        if (fieldName.contains(".")) {
            return fieldName;
        }

        // 根据字段名自动映射表别名
        if ("area_id".equals(fieldName)) {
            return "db.area_id";
        } else if ("building_id".equals(fieldName)) {
            return "df.building_id";
        } else if ("device_type".equals(fieldName) || "device_category".equals(fieldName) ||
                   "device_name".equals(fieldName) || "device_model".equals(fieldName)) {
            return "dd." + fieldName;
        } else if ("floor_id".equals(fieldName) || "subsystem_id".equals(fieldName) ||
                   "device_id".equals(fieldName) || "energy_value".equals(fieldName) || "date".equals(fieldName)) {
            return "f." + fieldName;
        }

        // 默认使用f表别名
        return "f." + fieldName;
    }

    /**
     * 构建对比分析查询SQL
     */
    private static BuiltSql buildCompareQuery(ChartDsl dsl) {
        Dataset dataset = dsl.getDataset();
        TimeRange timeRange = TimeRangeCalc.calculate(dsl.getTime().getWindow());
        Granularity granularity = Granularity.fromCode(dsl.getGranularity());

        List<Object> parameters = new ArrayList<>();
        StringBuilder sql = new StringBuilder();

        boolean isYoy = Boolean.TRUE.equals(dsl.getCompare().getYoy());
        boolean isMom = Boolean.TRUE.equals(dsl.getCompare().getMom());

        // 计算对比时间范围
        TimeRange compareTimeRange = calculateCompareTimeRange(timeRange, granularity, isYoy, isMom);

        // 是否需要时间轴补齐
        boolean needTimeFill = dsl.getFill() != null &&
                              Boolean.TRUE.equals(dsl.getFill().getCompleteTimeAxis());

        if (needTimeFill) {
            // 生成时间序列CTE
            sql.append(FillUtil.generateTimeSeriesCte(timeRange, granularity));
            sql.append(",\n");
        } else {
            sql.append("WITH ");
        }

        // 当期数据查询CTE
        sql.append("current_data AS (\n");
        appendDataQuery(sql, dataset, timeRange, granularity, dsl, parameters, "current");
        sql.append("\n),\n");

        // 对比期数据查询CTE
        sql.append("compare_data AS (\n");
        appendDataQuery(sql, dataset, compareTimeRange, granularity, dsl, parameters, "compare");
        sql.append("\n)");

        // 最终查询
        if (needTimeFill) {
            sql.append("\nSELECT \n");
            sql.append("  ts.bucket");

            // 添加维度字段
            if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
                for (DimensionSpec dim : dsl.getDimensions()) {
                    sql.append(",\n  COALESCE(cd.").append(dim.getName()).append(", '') AS ").append(dim.getName());
                }
            }

            // 根据对比类型返回不同的字段结构
            boolean fillZero = dsl.getFill() != null && Boolean.TRUE.equals(dsl.getFill().getFillZero());
            String defaultValue = fillZero ? "0" : "NULL";

            if (isYoy) {
                // 同比查询：返回去年值、今年值、同比增长率
                sql.append(",\n  COALESCE(cpd.value, ").append(defaultValue).append(") AS last_year_value");
                sql.append(",\n  COALESCE(cd.value, ").append(defaultValue).append(") AS current_year_value");
                sql.append(",\n  CASE WHEN COALESCE(cpd.value, 0) = 0 THEN NULL ");
                sql.append("ELSE ROUND((COALESCE(cd.value, 0) - COALESCE(cpd.value, 0)) / COALESCE(cpd.value, 0) * 100, 2) END AS yoy_growth_rate");
            } else if (isMom) {
                // 环比查询：返回上期值、当期值、环比增长率
                sql.append(",\n  COALESCE(cpd.value, ").append(defaultValue).append(") AS last_period_value");
                sql.append(",\n  COALESCE(cd.value, ").append(defaultValue).append(") AS current_period_value");
                sql.append(",\n  CASE WHEN COALESCE(cpd.value, 0) = 0 THEN NULL ");
                sql.append("ELSE ROUND((COALESCE(cd.value, 0) - COALESCE(cpd.value, 0)) / COALESCE(cpd.value, 0) * 100, 2) END AS mom_growth_rate");
            } else {
                // 默认返回当期值和对比值
                sql.append(",\n  COALESCE(cd.value, ").append(defaultValue).append(") AS current_value");
                sql.append(",\n  COALESCE(cpd.value, ").append(defaultValue).append(") AS compare_value");
            }

            sql.append("\nFROM time_series ts");
            sql.append("\nLEFT JOIN current_data cd ON ts.bucket = cd.bucket");
            sql.append("\nLEFT JOIN compare_data cpd ON ts.bucket = cpd.bucket");
        } else {
            sql.append("\nSELECT \n");
            sql.append("  cd.bucket");

            // 添加维度字段
            if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
                for (DimensionSpec dim : dsl.getDimensions()) {
                    sql.append(",\n  cd.").append(dim.getName());
                }
            }

            // 根据对比类型返回不同的字段结构
            if (isYoy) {
                // 同比查询：返回去年值、今年值、同比增长率
                sql.append(",\n  COALESCE(cpd.value, 0) AS last_year_value");
                sql.append(",\n  cd.value AS current_year_value");
                sql.append(",\n  CASE WHEN COALESCE(cpd.value, 0) = 0 THEN NULL ");
                sql.append("ELSE ROUND((cd.value - COALESCE(cpd.value, 0)) / COALESCE(cpd.value, 0) * 100, 2) END AS yoy_growth_rate");
            } else if (isMom) {
                // 环比查询：返回上期值、当期值、环比增长率
                sql.append(",\n  COALESCE(cpd.value, 0) AS last_period_value");
                sql.append(",\n  cd.value AS current_period_value");
                sql.append(",\n  CASE WHEN COALESCE(cpd.value, 0) = 0 THEN NULL ");
                sql.append("ELSE ROUND((cd.value - COALESCE(cpd.value, 0)) / COALESCE(cpd.value, 0) * 100, 2) END AS mom_growth_rate");
            } else {
                // 默认返回当期值和对比值
                sql.append(",\n  cd.value AS current_value");
                sql.append(",\n  COALESCE(cpd.value, 0) AS compare_value");
            }

            sql.append("\nFROM current_data cd");
            sql.append("\nLEFT JOIN compare_data cpd ON cd.bucket = cpd.bucket");

            // 添加维度匹配条件
            if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
                for (DimensionSpec dim : dsl.getDimensions()) {
                    sql.append(" AND cd.").append(dim.getName()).append(" = cpd.").append(dim.getName());
                }
            }
        }

        // 添加排序
        sql.append("\nORDER BY ");
        if (!CollectionUtils.isEmpty(dsl.getOrderBy())) {
            for (int i = 0; i < dsl.getOrderBy().size(); i++) {
                if (i > 0) sql.append(", ");
                OrderSpec order = dsl.getOrderBy().get(i);
                sql.append(order.getField()).append(" ").append(order.getDir());
            }
        } else {
            sql.append("bucket ASC");
        }

        return new BuiltSql(sql.toString(), parameters);
    }

    /**
     * 计算对比时间范围
     */
    private static TimeRange calculateCompareTimeRange(TimeRange currentRange, Granularity granularity, boolean isYoy, boolean isMom) {
        LocalDateTime currentStart = currentRange.getStart();
        LocalDateTime currentEnd = currentRange.getEnd();

        LocalDateTime compareStart;
        LocalDateTime compareEnd;

        if (isYoy) {
            // 同比：去年同期 - 使用相同的月份范围
            compareStart = currentStart.minusYears(1);
            compareEnd = currentEnd.minusYears(1);
        } else if (isMom) {
            // 环比：上个周期
            if (granularity == Granularity.MONTH) {
                compareStart = currentStart.minusMonths(1);
                compareEnd = currentEnd.minusMonths(1);
            } else if (granularity == Granularity.DAY) {
                compareStart = currentStart.minusMonths(1);
                compareEnd = currentEnd.minusMonths(1);
            } else {
                // 小时粒度的环比通常是前一天
                compareStart = currentStart.minusDays(1);
                compareEnd = currentEnd.minusDays(1);
            }
        } else {
            // 默认同比
            compareStart = currentStart.minusYears(1);
            compareEnd = currentEnd.minusYears(1);
        }

        return new TimeRange(compareStart, compareEnd);
    }

    /**
     * 构建数据查询子句
     */
    private static void appendDataQuery(StringBuilder sql, Dataset dataset, TimeRange timeRange,
                                       Granularity granularity, ChartDsl dsl, List<Object> parameters, String alias) {
        sql.append("  SELECT \n");
        sql.append("    ").append(granularity.getBucketExpr(dataset.getTimeField())).append(" AS bucket");

        // 添加维度字段
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                String selectExpr = getSelectExpression(dim);
                if (StringUtils.hasText(selectExpr)) {
                    sql.append(",\n    ").append(selectExpr).append(" AS ").append(dim.getName());
                }
            }
        }

        // 添加指标字段
        if (!CollectionUtils.isEmpty(dsl.getMetrics())) {
            for (MetricSpec metric : dsl.getMetrics()) {
                sql.append(",\n    ");
                String expr = StringUtils.hasText(metric.getExpr()) ? metric.getExpr() : dataset.getValueField();
                validateExpression(expr);
                sql.append(metric.getAgg()).append("(").append(expr).append(") AS ").append(metric.getName());
            }
        } else {
            // 默认指标
            sql.append(",\n    SUM(").append(dataset.getValueField()).append(") AS value");
        }

        sql.append("\n  FROM ").append(dataset.getTable()).append(" f");

        // 检查是否需要JOIN维度表
        boolean needFloorJoin = needsFloorJoin(dsl);
        boolean needBuildingJoin = needsBuildingJoin(dsl);
        boolean needDeviceJoin = needsDeviceJoin(dsl);

        if (needDeviceJoin) {
            sql.append("\n  LEFT JOIN dim_device dd ON f.device_id = dd.device_id");
        }
        if (needFloorJoin) {
            sql.append("\n  LEFT JOIN dim_floor df ON f.floor_id = df.floor_id");
        }
        if (needBuildingJoin) {
            sql.append("\n  LEFT JOIN dim_building db ON df.building_id = db.building_id");
        }

        // 添加时间条件
        sql.append("\n  WHERE f.").append(dataset.getTimeField()).append(" >= ?");
        sql.append("\n    AND f.").append(dataset.getTimeField()).append(" < ?");

        parameters.add(timeRange.getStart().format(DATETIME_FORMATTER));
        parameters.add(timeRange.getEnd().format(DATETIME_FORMATTER));

        // 添加过滤条件
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                sql.append("\n    AND ");
                buildFilterCondition(sql, filter, parameters);
            }
        }

        sql.append("\n  GROUP BY bucket");

        // 添加维度分组
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                String selectExpr = getSelectExpression(dim);
                if (StringUtils.hasText(selectExpr)) {
                    sql.append(", ").append(dim.getName());
                }
            }
        }
    }

    /**
     * 构建占比分析查询SQL
     */
    private static BuiltSql buildProportionQuery(ChartDsl dsl) {
        Dataset dataset = dsl.getDataset();
        TimeRange timeRange = TimeRangeCalc.calculate(dsl.getTime().getWindow());

        List<Object> parameters = new ArrayList<>();
        StringBuilder sql = new StringBuilder();

        sql.append("WITH ");

        // 详细数据查询CTE - 按维度分组
        sql.append("detail_data AS (\n");
        sql.append("  SELECT \n");

        // 添加维度字段作为分组依据
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (int i = 0; i < dsl.getDimensions().size(); i++) {
                if (i > 0) sql.append(",\n    ");
                DimensionSpec dim = dsl.getDimensions().get(i);

                // 根据维度字段选择合适的显示字段
                if ("floor_id".equals(dim.getName())) {
                    sql.append("COALESCE(df.floor_name, CONCAT(f.floor_id, 'F')) AS dimension_name");
                } else if ("building_id".equals(dim.getName())) {
                    sql.append("COALESCE(db.building_name, CONCAT('楼栋', db.building_id)) AS dimension_name");
                } else if ("area_id".equals(dim.getName())) {
                    sql.append("COALESCE(da.area_name, CONCAT('区域', da.area_id)) AS dimension_name");
                } else {
                    sql.append("f.").append(dim.getName()).append(" AS dimension_name");
                }
                break; // 占比分析只支持单维度
            }
        } else {
            sql.append("'总计' AS dimension_name");
        }

        // 添加指标字段
        if (!CollectionUtils.isEmpty(dsl.getMetrics())) {
            for (MetricSpec metric : dsl.getMetrics()) {
                sql.append(",\n    ");
                String expr = StringUtils.hasText(metric.getExpr()) ? metric.getExpr() : dataset.getValueField();
                validateExpression(expr);
                sql.append(metric.getAgg()).append("(").append(expr).append(") AS ").append(metric.getName());
            }
        } else {
            // 默认指标
            sql.append(",\n    SUM(").append(dataset.getValueField()).append(") AS value");
        }

        sql.append("\n  FROM ").append(dataset.getTable()).append(" f");

        // 检查是否需要JOIN维度表
        boolean needFloorJoin = needsFloorJoin(dsl) || hasDimension(dsl, "floor_id");
        boolean needBuildingJoin = needsBuildingJoin(dsl) || hasDimension(dsl, "building_id");
        boolean needAreaJoin = needsAreaJoin(dsl) || hasDimension(dsl, "area_id");
        boolean needDeviceJoin = needsDeviceJoin(dsl) || hasDimension(dsl, "device_type") ||
                                hasDimension(dsl, "device_category") || hasDimension(dsl, "device_name") ||
                                hasDimension(dsl, "device_model");

        if (needDeviceJoin) {
            sql.append("\n  LEFT JOIN dim_device dd ON f.device_id = dd.device_id");
        }
        if (needFloorJoin) {
            sql.append("\n  LEFT JOIN dim_floor df ON f.floor_id = df.floor_id");
        }
        if (needBuildingJoin) {
            sql.append("\n  LEFT JOIN dim_building db ON df.building_id = db.building_id");
        }
        if (needAreaJoin) {
            sql.append("\n  LEFT JOIN dim_area da ON db.area_id = da.area_id");
        }

        // 添加时间条件
        sql.append("\n  WHERE f.").append(dataset.getTimeField()).append(" >= ?");
        sql.append("\n    AND f.").append(dataset.getTimeField()).append(" < ?");

        parameters.add(timeRange.getStart().format(DATETIME_FORMATTER));
        parameters.add(timeRange.getEnd().format(DATETIME_FORMATTER));

        // 添加所有过滤条件
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                sql.append("\n    AND ");
                buildFilterCondition(sql, filter, parameters);
            }
        }

        // 按维度分组
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            sql.append("\n  GROUP BY ");
            for (int i = 0; i < dsl.getDimensions().size(); i++) {
                if (i > 0) sql.append(", ");
                DimensionSpec dim = dsl.getDimensions().get(i);

                if ("floor_id".equals(dim.getName())) {
                    sql.append("f.floor_id, df.floor_name");
                } else if ("building_id".equals(dim.getName())) {
                    sql.append("db.building_id, db.building_name");
                } else if ("area_id".equals(dim.getName())) {
                    sql.append("da.area_id, da.area_name");
                } else {
                    sql.append("f.").append(dim.getName());
                }
                break; // 占比分析只支持单维度
            }
        }

        sql.append("\n),\n");

        // 总计数据查询CTE
        sql.append("total_data AS (\n");
        sql.append("  SELECT \n");
        sql.append("    SUM(").append(dataset.getValueField()).append(") AS total_value");

        sql.append("\n  FROM ").append(dataset.getTable()).append(" f");

        if (needDeviceJoin) {
            sql.append("\n  LEFT JOIN dim_device dd ON f.device_id = dd.device_id");
        }
        if (needFloorJoin) {
            sql.append("\n  LEFT JOIN dim_floor df ON f.floor_id = df.floor_id");
        }
        if (needBuildingJoin) {
            sql.append("\n  LEFT JOIN dim_building db ON df.building_id = db.building_id");
        }

        // 添加时间条件
        sql.append("\n  WHERE f.").append(dataset.getTimeField()).append(" >= ?");
        sql.append("\n    AND f.").append(dataset.getTimeField()).append(" < ?");

        parameters.add(timeRange.getStart().format(DATETIME_FORMATTER));
        parameters.add(timeRange.getEnd().format(DATETIME_FORMATTER));

        // 添加非维度过滤条件（排除维度字段的过滤条件）
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                // 跳过维度字段的过滤条件，因为总计需要包含所有维度
                if (!isDimensionField(filter.getField(), dsl)) {
                    sql.append("\n    AND ");
                    buildFilterCondition(sql, filter, parameters);
                }
            }
        }

        sql.append("\n)");

        // 最终查询 - 按维度返回占比
        sql.append("\nSELECT \n");
        sql.append("  dd.dimension_name AS bucket");
        sql.append(",\n  dd.value AS detail_value");
        sql.append(",\n  CASE WHEN COALESCE(td.total_value, 0) = 0 THEN NULL ");
        sql.append("ELSE ROUND(dd.value / COALESCE(td.total_value, 0) * 100, 2) END AS proportion_rate");

        sql.append("\nFROM detail_data dd");
        sql.append("\nCROSS JOIN total_data td");

        // 添加排序
        sql.append("\nORDER BY ");
        if (!CollectionUtils.isEmpty(dsl.getOrderBy())) {
            for (int i = 0; i < dsl.getOrderBy().size(); i++) {
                if (i > 0) sql.append(", ");
                OrderSpec order = dsl.getOrderBy().get(i);
                sql.append(order.getField()).append(" ").append(order.getDir());
            }
        } else {
            sql.append("bucket ASC");
        }

        return new BuiltSql(sql.toString(), parameters);
    }

    /**
     * 判断字段是否为维度字段
     */
    private static boolean isDimensionField(String fieldName, ChartDsl dsl) {
        if (CollectionUtils.isEmpty(dsl.getDimensions())) {
            return false;
        }

        for (DimensionSpec dim : dsl.getDimensions()) {
            if (fieldName.equals(dim.getName())) {
                return true;
            }
        }

        return false;
    }

    /**
     * 检查是否需要JOIN区域表
     */
    private static boolean needsAreaJoin(ChartDsl dsl) {
        // 检查维度中是否包含area_id
        if (!CollectionUtils.isEmpty(dsl.getDimensions())) {
            for (DimensionSpec dim : dsl.getDimensions()) {
                if ("area_id".equals(dim.getName())) {
                    return true;
                }
            }
        }

        // 检查过滤条件中是否包含area_id
        if (!CollectionUtils.isEmpty(dsl.getFilters())) {
            for (FilterSpec filter : dsl.getFilters()) {
                if ("area_id".equals(filter.getField())) {
                    return true;
                }
            }
        }

        return false;
    }

    /**
     * 检查是否包含指定维度
     */
    private static boolean hasDimension(ChartDsl dsl, String dimensionName) {
        if (CollectionUtils.isEmpty(dsl.getDimensions())) {
            return false;
        }

        for (DimensionSpec dim : dsl.getDimensions()) {
            if (dimensionName.equals(dim.getName())) {
                return true;
            }
        }

        return false;
    }
}
